package com.cosina.mr.dao;

import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.cosina.mr.Constants;
import com.cosina.mr.R;
import com.cosina.mr.model.MoneyEvent;

public class MoneyRecordDB extends SQLiteOpenHelper implements IMoneyRecordDAO, IStatisticsDAO{

	public final static String DB_NAME = "mr"; 
	public final static String TABLE_NAME = "moneyRecord"; 
	public final static int VERSION = 20090607;
	
	private Context context;
	
	MoneyRecordDB(Context context) {
		super(context, DB_NAME, null, VERSION);
		this.context = context;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.beginTransaction();
		try{
			db.execSQL(context.getString(R.string.CREATE_DB));
			db.setTransactionSuccessful();
		}catch(SQLException ex){
			Log.e(Constants.TAG_NAME, "error occur in onCreate():" + ex.getMessage());
			ex.printStackTrace();
		}finally{
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//do nothing;
	}

	@Override
	public void add(MoneyEvent newEvent) {
		Log.e(Constants.TAG_NAME, "add()");
		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();
		try{
			db.execSQL(context.getString(R.string.ADD_RECORD),newEvent.getInsertArg());
			db.setTransactionSuccessful();
		}catch(SQLException ex){
			Log.e(Constants.TAG_NAME, "error occur in add():" + ex.getMessage());
			ex.printStackTrace();
		}finally{
			db.endTransaction();
		}
	}

	@Override
	public Cursor findAll() {
		return this.getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
	}

	@Override
	public void remove(long id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.beginTransaction();
		try{
			db.delete(TABLE_NAME, "_id=?", new String[]{id + ""});
			db.setTransactionSuccessful();
		}catch(SQLException ex){
			Log.e(Constants.TAG_NAME, "error occur in remove():" + ex.getMessage());
			ex.printStackTrace();
		}finally{
			db.endTransaction();
		}
	}

	@Override
	public Cursor findBy(long id) {
		SQLiteDatabase db = this.getReadableDatabase();
		return db.query(TABLE_NAME, null, "_id=?", new String[]{id + ""}, null, null, null);
	}

	@Override
	public void update(MoneyEvent newEvent) {
		Log.e(Constants.TAG_NAME, "update()");
		
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put(MoneyEvent.NAME,newEvent.getName());
		cv.put(MoneyEvent.DESC,newEvent.getDesc());
		cv.put(MoneyEvent.OCCURTIME,newEvent.getOccurTime());
		cv.put(MoneyEvent.MONEY,newEvent.getMoney());
		
		db.beginTransaction();
		try{
			db.update(TABLE_NAME, cv, "_id=?", new String[]{newEvent.getId()});
			db.setTransactionSuccessful();
		}catch(SQLException ex){
			Log.e(Constants.TAG_NAME, "error occur in remove():" + ex.getMessage());
			ex.printStackTrace();
		}finally{
			db.endTransaction();
		}
	}

	@Override
	public Map<String,Integer> queryTotal() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, new String[]{"sum("+ MoneyEvent.MONEY+")",MoneyEvent.NAME}, null, null, MoneyEvent.NAME, null, null);
		int sumMoneyIndex = cursor.getColumnIndex("sum("+ MoneyEvent.MONEY+")");
		int nameIndex = cursor.getColumnIndex(MoneyEvent.NAME);
		Map<String,Integer> result = new HashMap<String,Integer>();
		while(cursor.isAfterLast() == false){
			if(cursor.moveToNext() == false)
				break;
			Integer sumMoney = cursor.getInt(sumMoneyIndex);
			String name = cursor.getString(nameIndex);
			result.put(name, sumMoney);
		}
		cursor.close();
		return result;
	}
}
